-- add date column to ShipmentSchedule
ALTER TABLE 
   Shipmentschedule
ADD 
   (
      PLANNED_START_DATE   Date,
      PLANNED_END_DATE   Date
   );
   
 -- add date column to Shipment
ALTER TABLE 
   Shipment 
ADD 
   (
      PLANNED_START_DATE   Date,
      PLANNED_DELIVERY_DATE   Date
   );

-- create auto increment trackingNo column in ShipmentLine

alter table
   ShipmentLine
rename column
   N1 
TO
   TrackingNo;

CREATE SEQUENCE TrackingNo
    START WITH 1000
    INCREMENT BY 1;
   
CREATE OR REPLACE TRIGGER add_tracking_no
  BEFORE INSERT ON ShipmentLine
  FOR EACH ROW
BEGIN
  :new.TrackingNo := TrackingNo.nextval;
END;

-- Shipment line no
CREATE SEQUENCE ShipmentLineNo
    START WITH 4
    INCREMENT BY 1;
   
CREATE OR REPLACE TRIGGER ShipmentLineNo
  BEFORE INSERT ON ShipmentLine
  FOR EACH ROW
BEGIN
  :new.ShipmentLineNo := ShipmentLineNo.nextval;
END;


-- added column current_port in schedule
alter table
   ShipmentSchedule
rename column
   S1 
TO
   Current_Port;


   CREATE SEQUENCE ShipmentId
    START WITH 10000
    INCREMENT BY 1;
   
CREATE OR REPLACE TRIGGER ShipmentId
  BEFORE INSERT ON Shipment
  FOR EACH ROW
BEGIN
  :new.ShipmentId := ShipmentId.nextval;
END;